﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DataAccess
{
    public class Lop_DA
    {
        QLHocSinhDataContext ql = new QLHocSinhDataContext();
        public List<LOP> LoadData()
        {
            return ql.LOPs.ToList();
        }
        public string Laykhoi(int khoi)
        {
            if (khoi == 10)
            {
                return ql.QUYDINHLOPs.Select(p => p.SLLOP10).FirstOrDefault().ToString();
            }
            else if (khoi == 11)
            {
                return ql.QUYDINHLOPs.Select(p => p.SLLOP11).FirstOrDefault().ToString();
            }
            else
            {
                return ql.QUYDINHLOPs.Select(p => p.SLLOP12).FirstOrDefault().ToString();
            }
        }
        public string Demkhoi(int khoi)
        {
            var str = from q in ql.LOPs
                      where q.KHOI == khoi
                      select new { q.KHOI };
            return str.ToList().Count.ToString();
        }
        public string Layss()
        {
            return ql.LOPs.Select(p => p.SISO).FirstOrDefault().ToString();
        }
        public bool Them(string tenlop, int ss, int khoi)
        {
            LOP l = new LOP();
            try
            {
                l.TENLOP = tenlop;
                l.SISO = ss;
                l.KHOI = khoi;
                ql.LOPs.InsertOnSubmit(l);
                ql.SubmitChanges();
                return true;

            }
            catch
            {
                return false;
            }
        }
        public bool Sua(string tenlop, int ss, int khoi)
        {
            LOP l = ql.LOPs.Where(p => p.TENLOP == tenlop).FirstOrDefault();
            l.SISO = ss;
            l.KHOI = khoi;
            try
            {
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool Xoa(string tenlop)
        {
            LOP l = ql.LOPs.Where(p => p.TENLOP == tenlop).FirstOrDefault();
            try
            {
                ql.LOPs.DeleteOnSubmit(l);
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public void Updatess(string tenlop, int ss)
        {
            LOP l = ql.LOPs.Where(p => p.TENLOP == tenlop).FirstOrDefault();
            l.SISO = ss;
            ql.SubmitChanges();
        }
        public void CapnhatSSTKHK(string tenlop, int ss)
        {
            TONGKETHK t = ql.TONGKETHKs.Where(p=>p.TENLOP==tenlop).Where(o=>o.HOCKY=="Học Kỳ I").FirstOrDefault();
            t.SISO = ss;
            TONGKETHK k = ql.TONGKETHKs.Where(p => p.TENLOP == tenlop).Where(o => o.HOCKY == "Học Kỳ II").FirstOrDefault();
            k.SISO = ss;
            ql.SubmitChanges();
        }
        //............thay doi qd ...............
        public bool Thaydoiqd(int ssl10, int sll11, int sll12, int ss)
        {
            QUYDINHLOP q = ql.QUYDINHLOPs.Where(p => p.MATHAYDOI == 1).FirstOrDefault();
            q.SLLOP10 = ssl10;
            q.SLLOP11 = sll11;
            q.SLLOP12 = sll12;
            q.SISO = ss;
            try
            {
                ql.SubmitChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public string Layssqd()
        {
            return ql.QUYDINHLOPs.Select(p => p.SISO).FirstOrDefault().ToString();
        }
        //...............them tkhk
        public void ThemLopTKHK(string tl, string hk)
        {
            TONGKETHK t = new TONGKETHK();
            t.TENLOP = tl;
            t.HOCKY = hk;
            t.SOLUONGDAT = 0;
            ql.TONGKETHKs.InsertOnSubmit(t);
            ql.SubmitChanges();
        }
        public void XoaLopTKHK(string tl)
        {
            TONGKETHK t = ql.TONGKETHKs.Where(p => p.TENLOP == tl).Where(o=>o.HOCKY=="Học Kỳ I").FirstOrDefault();
            TONGKETHK k = ql.TONGKETHKs.Where(p => p.TENLOP == tl).Where(o => o.HOCKY == "Học Kỳ II").FirstOrDefault();
            ql.TONGKETHKs.DeleteOnSubmit(t);
            ql.TONGKETHKs.DeleteOnSubmit(k);
            ql.SubmitChanges();
        }
        //............Tong ket hoc ky.............
        public List<TONGKETHK> Loadtb()
        {
            return ql.TONGKETHKs.ToList();
        }
        public List<Object> Loaddt(string hk)
        {
            var sql = from k in ql.TONGKETHKs
                      where k.HOCKY == hk
                      select new { k.TENLOP, k.SISO, k.SOLUONGDAT, k.TILE };
            return sql.ToList<Object>();

        }
        //............TBHK.............
        public List<TBHOCKY> LoadTBHK()
        {
            return ql.TBHOCKies.ToList();
        }
        public List<Object> TK(int loai, string dk)
        {
            if (loai == 0)
            {
                var sql = from k in ql.TBHOCKies
                          where k.HOTENHS.Contains(dk)
                          select new { k.STT, k.HOTENHS, k.LOP, k.TBHK1, k.TBHK2 };
                return sql.ToList<Object>();
            }
            else
            {
                var sql = from k in ql.TBHOCKies
                          where k.LOP.Contains(dk)
                          select new { k.STT, k.HOTENHS, k.LOP, k.TBHK1, k.TBHK2 };
                return sql.ToList<Object>();
            }
        }
        //...........................
        public bool KTTTL(string lop)
        {
            int kt = ql.HOCSINHLOPs.Where(p=>p.LOP==lop).Count();
            if (kt != 0)
                return true;
            else
                return false;
        }
    }
}
